package com.fangtao.service;
import com.fangtao.dao.UserDAO;
import com.fangtao.domain.User;
import com.fangtao.utils.JDBCUtilsByDruid;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
 * @ClassName UserService
 * @Description TODO
 * @date 2022/9/22 16:53
 * @author: fangtao
 * @Version 1.0
 */
public class UserService {
    private UserDAO userDAO=new UserDAO();

    //登录验证方法，返回一个user对象
    public User Login(String  name,String paswd){

        String  sql="SELECT * FROM `smbms_user`  WHERE userCode=? and userPassword=?;";
        return userDAO.querySingle(sql, User.class, name, paswd);


    }
    //修改密码的方法
    public  boolean updatePwd(int  id,String NewPassword){
        String sql="UPDATE `smbms_user` SET userPassword = ? WHERE  id = ?;";
        int update = userDAO.update(sql, NewPassword, id);
        if (update>0){
            return true;
        }else {
            return false;
        }

    }

    //根据user_name模糊匹配和权限名称来查询总数
    public  int getCount(String user_name,int id){
        int count=0;
      StringBuffer sql= new StringBuffer("SELECT count(1) as count FROM smbms_user  u,smbms_role r WHERE  u.userRole=r.id");
      if (user_name!=null){
          sql.append(" and  u.userName LIKE '%"+user_name+"%' ");
          Object o = userDAO.queryScalar(sql.toString());
          count=Integer.valueOf(o.toString());
      }if (id>0){
          sql.append(" and r.id=?");
          Object o = userDAO.queryScalar(sql.toString(), id);
          count=Integer.valueOf(o.toString());
      }else {
            Object o = userDAO.queryScalar(sql.toString());
            count=Integer.valueOf(o.toString());
        }
        return count;
    }
//根据id查询user表，新增userRoleName项的查询
    public User getUserById(int id){
        String sql="select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.id=? and u.userRole = r.id";
        User user = userDAO.querySingle(sql, User.class, id);
        return user;
    }


    //根据user_name模糊匹配和权限名称来查询userList
    public List<User>getUserList(String user_name,int id,int currentPageNo, int pageSize){
         List<User> list=null;
        StringBuffer sql= new StringBuffer("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
        if (user_name!=null&&!user_name.equals("")){
            sql.append(" and  u.userName LIKE '%"+user_name+"%' ");
//            System.out.println(sql.toString());
//            list= userDAO.queryMulti(sql.toString(), User.class);

        }
        if (id>0){
            sql.append(" and r.id=?");
//            list= userDAO.queryMulti(sql.toString(), User.class,id);
//            System.out.println(sql);

        }
        sql.append(" order by creationDate DESC limit ?,?");
        currentPageNo = (currentPageNo-1)*pageSize;
        if (id>0)  {
            list= userDAO.queryMulti(sql.toString(), User.class,id,currentPageNo,pageSize);
        }else {
            list= userDAO.queryMulti(sql.toString(), User.class,currentPageNo,pageSize);
        }


     return list;
    }


//    根据用户的usercode来判断用户名是否可用
    public User selectUserCodeExist(String userCode){
      String sql="SELECT *  FROM smbms_user WHERE userCode=?";
        User user = userDAO.querySingle(sql, User.class, userCode);
        return user;
    }
//    传入User对象来插入数据
    public boolean add(User user){
        boolean flag = false;
//        Connection connection = null;
        try {
//            connection = JDBCUtilsByDruid.getConnection();
//            connection.setAutoCommit(false);//开启JDBC事务管理
            int updateRows = userDAO.add(user);
//            connection.commit();
            if(updateRows > 0){
                flag = true;
                System.out.println("add success!");
            }else{
                System.out.println("add failed!");
            }

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("rollback==================");
//                connection.rollback();
        }
        return flag;
    }
//    根据传入的id来删除数据
    public int deleteUserById(Integer id){
   String sql="DELETE FROM smbms_user  WHERE id = ?";

        int update = userDAO.update(sql, id);
        return update;
    }
//    根据id来修改数据
    public boolean modify(User user){
        // TODO Auto-generated method stub
        Connection connection = null;
        boolean flag = false;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            if(userDAO.modify(connection,user) > 0)
                flag = true;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            JDBCUtilsByDruid.close(null, null, connection);
        }
        return flag;
    }
    @Test
    public void test(){
//        User admin = selectUserCodeExist("admin");
//        System.out.println(admin.getUserCode());
        int i = deleteUserById(14);
        System.out.println(i);

//        User userById = getUserById(1);
//        System.out.println(userById.getUserRoleName());
//        int count = getCount(null, 0);
//        System.out.println(count);

//        User admin = Login("admin", "1234567");
//        System.out.println(admin);
//        List<User> userList = getUserList(null, 0,1,10);
//        for (User user : userList) {
//            System.out.println(user.toString());
//        }
    }


}
